package database;

import java.util.ArrayList;
import java.util.List;

import toDo.app.R;
import toDo.app.models.ToDoListItem;
import toDo.app.models.User;
import android.content.ContentValues;
import android.content.Context;

class ListDatabaseConnector extends DatabaseTableTemplate {
	public static final String TABLE_NAME = "list"; //$NON-NLS-1$
	public static final String COLUMN_ID = "_id"; //$NON-NLS-1$
	public static final String COLUMN_USERNAME = "username"; //$NON-NLS-1$
	public static final String COLUMN_ITEM_NAME = "item_name"; //$NON-NLS-1$
	public static final String COLUMN_DESCRIPTION = "description"; //$NON-NLS-1$
	public static final String COLUMN_COMMENTS = "comments"; //$NON-NLS-1$
	public static final String COLUMN_TIME = "time"; //$NON-NLS-1$
	public static final String COLUMN_LOCATION = "location"; //$NON-NLS-1$
	public static final String COLUMN_COMPLETED = "completed"; //$NON-NLS-1$
	public static final String COLUMN_TYPE = "type"; //$NON-NLS-1$

	
	
	public static final String DATABASE = "CREATE TABLE " + TABLE_NAME + "( "  //$NON-NLS-1$ //$NON-NLS-2$
			+ COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " //$NON-NLS-1$
			+ COLUMN_USERNAME + " TEXT NOT NULL, " //$NON-NLS-1$
			+ COLUMN_ITEM_NAME + " TEXT NOT NULL, " //$NON-NLS-1$
			+ COLUMN_DESCRIPTION + " TEXT, " //$NON-NLS-1$
			+ COLUMN_COMMENTS + " TEXT, " //$NON-NLS-1$
			+ COLUMN_LOCATION + " TEXT, " //$NON-NLS-1$
			+ COLUMN_TYPE + " TEXT, " //$NON-NLS-1$
			+ COLUMN_COMPLETED + " INTEGER DEFAULT 0, " //$NON-NLS-1$
			+ COLUMN_TIME + " INTEGER DEFAULT 0," //$NON-NLS-1$
			+ "FOREIGN KEY (" + COLUMN_USERNAME + ") REFERENCES "  //$NON-NLS-1$ //$NON-NLS-2$
					+ UserDatabaseConnector.TABLE_NAME 
					+ " (" + UserDatabaseConnector.COLUMN_USERNAME + ") ON DELETE CASCADE ON UPDATE CASCADE);"; //$NON-NLS-1$ //$NON-NLS-2$

	
	public ListDatabaseConnector(Context context) {
		super(context);
	}

	public void addToDoListItem(User user, ToDoListItem item){
		doOpen();
		ContentValues values = new ContentValues();
		values.put(COLUMN_USERNAME, user.getUsername());
		values.put(COLUMN_ITEM_NAME, item.itemName);
		values.put(COLUMN_DESCRIPTION, item.description);
		values.put(COLUMN_COMMENTS, item.comments);
		values.put(COLUMN_LOCATION, item.location);
		values.put(COLUMN_TIME, item.time);
		values.put(COLUMN_TYPE, item.type);
		values.put(COLUMN_COMPLETED, item.completed);
		this.connector.insert(TABLE_NAME, null, values);
		doClose();
	}
	
	public void changeToDoListItem(User user, ToDoListItem item){
		doOpen();
		ContentValues values = new ContentValues();
		values.put(COLUMN_USERNAME, user.getUsername());
		values.put(COLUMN_ITEM_NAME, item.itemName);
		values.put(COLUMN_DESCRIPTION, item.description);
		values.put(COLUMN_COMMENTS, item.comments);
		values.put(COLUMN_LOCATION, item.location);
		values.put(COLUMN_TYPE, item.type);
		values.put(COLUMN_TIME, item.time);
		values.put(COLUMN_COMPLETED, item.completed);
		String whereClause = COLUMN_ID + " = " + item._id; //$NON-NLS-1$
		this.connector.update(TABLE_NAME, values, whereClause, null);
		doClose();
	}

	public List<ToDoListItem> getList(User user){
		return getList(user, null, "-1", true, null, null); //$NON-NLS-1$
	}
	
	public List<ToDoListItem> getListByType(User user, String type){
		return getList(user, type, "-1", true, null, null); //$NON-NLS-1$
	}
	
	public List<ToDoListItem> getListByDate(User user,  int date, boolean after){
		return getList(user, null, String.valueOf(date), after, null, null);
	}
	
	public List<ToDoListItem> getListByCompleted(User user, boolean completed){
		return getList(user, null, "-1", true, null, String.valueOf(completed)); //$NON-NLS-1$
	}
	
	private List<ToDoListItem> getList(User user, String type, String date, boolean after, String limit, String completed){
		List<ToDoListItem> list = new ArrayList<ToDoListItem>();
		doOpen();
		String timeCompare = (after)? ">=":"<"; //$NON-NLS-1$ //$NON-NLS-2$
		String where = COLUMN_USERNAME + " = ? " + " AND " + COLUMN_TIME + timeCompare + " ? "; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		String[] whereArgs;
		if(completed != null){
			 where += " AND " + COLUMN_COMPLETED + (Boolean.parseBoolean(completed)?" <> 0":" = 0"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		}
		if(type != null){
			where += " AND " + COLUMN_TYPE + " = ?"; //$NON-NLS-1$ //$NON-NLS-2$
			whereArgs = new String[]{user.getUsername(),date,type};			
		} else {
			whereArgs = new String[]{user.getUsername(),date};
		}
		this.cursor = this.connector.query(TABLE_NAME,
				new String[]{COLUMN_ITEM_NAME,COLUMN_DESCRIPTION,COLUMN_COMMENTS,
						COLUMN_TIME,COLUMN_TYPE,COLUMN_ID,COLUMN_LOCATION,COLUMN_COMPLETED}, 
				where, whereArgs, 
				null, null, COLUMN_ID + " DESC", limit); //$NON-NLS-1$
		this.cursor.moveToFirst();
		while (!this.cursor.isAfterLast()) {
			//String name, String description, String comments, int time, String type, int _id, int completed
			ToDoListItem item = new ToDoListItem(
					this.cursor.getString(0),	//name
					this.cursor.getString(1),	//description
					this.cursor.getString(2),	//comment
					this.cursor.getInt(3),		//time
					this.cursor.getString(4),	//type
					this.cursor.getInt(5),		//id
					this.cursor.getString(6),
					this.cursor.getInt(7));		//completed
			list.add(item);
			this.cursor.moveToNext();
		}
		doClose();
		return list;
	}
	
	public List<String> getTypes(User user){
		List<String> list = new ArrayList<String>();
		doOpen();
		this.cursor = this.connector.query(true, TABLE_NAME,
				new String[]{COLUMN_TYPE}, COLUMN_USERNAME + " = ? ", new String[]{user.getUsername()},  //$NON-NLS-1$
						null, null, COLUMN_TYPE + " COLLATE NOCASE", null); //$NON-NLS-1$
		this.cursor.moveToFirst();
		while (!this.cursor.isAfterLast()) {
			list.add(this.cursor.getString(0));
			this.cursor.moveToNext();
		}
		doClose();
		addToListAlphabetacally(list, this.context.getString(R.string.school), true);
		addToListAlphabetacally(list, this.context.getString(R.string.personal), true);
		addToListAlphabetacally(list, this.context.getString(R.string.work), true);
		list.remove(null);
		list.remove(""); //$NON-NLS-1$
		list.add(0, this.context.getString(R.string.all));
		return list;
	}
	
	@Override
	protected String getTableStructure() {
		return DATABASE;
	}
}
